"""
Case Type   : D库常用语法兼容
Case Name   : A库use current_database use schema_name 均不支持
Create At   : 2025-11-20
Owner       : chen
Description :
    1.创建a库和schema
    2.登入a库创建schema
    3.use不存在的schema_name / use database_name
    4.use schema_name;
    5.use 其他数据库的schema
    6.清理环境
Expect      :
    1.成功
    2.成功
    3.合理报错
    4.合理报错
    5.合理报错
    6.清理环境
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class DDL_USE_Case0003(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.dbname = 'db_use_case_003'
        self.not_exists_dbname = 'db_use_not_exists_case_003'
        self.schema1_name = 'sa1_use_case_003'
        self.schema2_name = 'sa2_use_case_003'
        self.not_exists_schema_name = 'sa_use_not_exists_case_003'

    def test_Function_Commonviews(self):
        text = '-----step1: 创建a库; expect: 成功-----'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_db = f'''drop database if exists {self.dbname};
            create database if not exists {self.dbname} dbcompatibility = 'A';'''
        res = primary_sh.execut_db_sql(create_db)
        log.info(res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res)
        create_sa = f'drop schema if exists {self.schema1_name};' \
            f'create schema if not exists {self.schema1_name};'
        res = primary_sh.execut_db_sql(create_sa)
        log.info(res)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, res)

        text = '-----step2: 登入a库创建schema; expect: 成功-----'
        log.info(text)
        create_sa = f'drop schema if exists {self.schema2_name};' \
            f'create schema if not exists {self.schema2_name};'
        res = primary_sh.execut_db_sql(create_sa, dbname=self.dbname)
        log.info(res)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, res)

        text = '-----step3: use不存在的schema/db; expect: 合理报错-----'
        log.info(text)
        use = primary_sh.execut_db_sql(f'use {self.not_exists_dbname};',
                                     dbname=self.dbname)
        log.info(use)
        self.assertIn('ERROR:  syntax error at or near "use"', use)
        use = primary_sh.execut_db_sql(f'use {self.not_exists_schema_name};',
                                     dbname=self.dbname)
        log.info(use)
        self.assertIn('ERROR:  syntax error at or near "use"', use)

        text = '-----step4: use schema_name; expect: 合理报错-----'
        log.info(text)
        use = primary_sh.execut_db_sql(f'use {self.schema2_name};',
                                     dbname=self.dbname)
        log.info(use)
        self.assertIn('ERROR:  syntax error at or near "use"', use)

        text = '-----step5: use其他数据库的schema; expect: 合理报错-----'
        log.info(text)
        use = primary_sh.execut_db_sql(f'use {self.schema1_name};',
                                     dbname=self.dbname)
        log.info(use)
        self.assertIn('ERROR:  syntax error at or near "use"', use)
    
    def tearDown(self):
        text = '-----step6: 清理环境; expect: 成功-----'
        log.info(text)
        drop_schema = f'drop schema if exists {self.schema2_name};'
        res = primary_sh.execut_db_sql(drop_schema,
                                     dbname=self.dbname)
        log.info(res)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, res)
        drop_schema = f'drop schema if exists {self.schema1_name};'
        res = primary_sh.execut_db_sql(drop_schema)
        log.info(res)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, res)
        drop_db = f'drop database if exists {self.dbname};'
        res = primary_sh.execut_db_sql(drop_db)
        log.info(res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res)